# 导入 did
# 2022-03-08
# yeyi

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell import MergedCell
import pymysql
import os
import time
import traceback

# 输出文件名
fileName = 'did'
fileNameTimeStr = time.strftime('%Y-%m-%d_%H-%M-%S',time.localtime(time.time()))
logFileName = fileName + '_' + fileNameTimeStr

def printt(s):
    f = open(logFileName+'.log', 'a+', encoding='utf-8')
    print(str(s),file=f)
    f.close()
    print(s)

# 统一处理异常
def dealException(e):
    f = open(logFileName+'.log', 'a+', encoding='utf-8')
    traceback.print_exc(file=f)
    print(str(e),file=f)
    f.close()
    print(str(e))


def parser_merged_cell(sheet: Worksheet, row, col):
    """
    检查是否为合并单元格并获取对应行列单元格的值。
    如果是合并单元格，则取合并区域左上角单元格的值作为当前单元格的值,否则直接返回该单元格的值
    :param sheet: 当前工作表对象
    :param row: 需要获取的单元格所在行
    :param col: 需要获取的单元格所在列
    :return: 
    """
    cell = sheet.cell(row=row, column=col)
    if cell.value is not None:
        return cell

    if isinstance(cell, MergedCell):  # 判断该单元格是否为合并单元格
        for merged_range in sheet.merged_cell_ranges:  # 循环查找该单元格所属的合并区域
            if cell.coordinate in merged_range:
                # 获取合并区域左上角的单元格作为该单元格的值返回
                cell = sheet.cell(row=merged_range.min_row, column=merged_range.min_col)
                break
    return cell

# 联调
db = pymysql.connect(host='10.2.3.5',
                     port=3306,
                     user='root',
                     passwd='sirun@2018',
                     db='bss-gq',
                     charset='utf8')
cursor = db.cursor()  # 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook(os.getcwd() +
                            "\\能力集脚本\\excel\\A58Did.xlsx")
# 获取workbook中所有的表格
sheets = wb.sheetnames

# did_id 比 ecu_id 增加量
number = 0
# 目标 ecuId
ecuIds = []
# 连接 ecuId
# for i in range(0,24):
#     num=5858000001+i
#     ecuIds.append(str(num))

# excel 标签列表
sheetLabels = [
    'IBCM(BCM)',
    'TBOX',
    'SBM'
    ]

# 对应上边标签顺序的 ecuId
ecuIds = [
    '5858000005',
    '5858000011',
    '5858000022'
]

item = {}
for sheet in sheets:
    for j in range(0, len(sheetLabels)):
        sheetLabel = sheetLabels[j]
        if sheetLabel != sheet:
            continue

        ecuId = ecuIds[j]
        ws = wb[sheet]
        for i in range(2, ws.max_row + 1):
            didCode = parser_merged_cell(ws,i,1).value
            # 防止 ws.max_row 过大
            if didCode is None:
                continue

            # did_id = ecu_id+3位自增（000开始自增）
            nu = '%03d' % number
            item['did_id'] = ecuId+nu
            item['ecu_id'] = ecuId
            item['did_code'] = int(didCode, 16)
            if item['did_code'] > 57360:
                print('')
            item['did_name'] = parser_merged_cell(ws,i,2).value
            item['did_name_cn'] = item['did_name']
            item['did_len'] = parser_merged_cell(ws, i, 3).value
            item['sub_data_name'] = ws.cell(row=i, column=6).value
            item['sub_data_name_cn'] = ws.cell(row=i, column=6).value
            item['data_type'] = parser_merged_cell(ws, i,12).value
            item['app_support'] = parser_merged_cell(ws, i,13).value
            item['did_byte'] = parser_merged_cell(ws, i, 4).value
            item['did_bit'] = ws.cell(row=i, column=5).value
            # item['max_val'] = ws.cell(row=i, column=9).value
            # item['min_val'] = ws.cell(row=i, column=8).value
            item['max_val'] = ''
            item['min_val'] = ''
            printt('item ecuid: '+ecuId+' i: '+str(i)+' '+str(item))
            into = "insert into `tab_did` (`did_id`, `ecu_id`, `did_code`, `did_name`, `did_name_cn`,`did_len`,`sub_data_name`,`sub_data_name_cn`,`data_type`,`app_support`,`did_byte`,`did_bit`,`max_val`,`min_val`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            values = (item['did_id'],item['ecu_id'],item['did_code'],item['did_name'],item['did_name_cn'],item['did_len'],item['sub_data_name'],item['sub_data_name_cn'],item['data_type'],item['app_support'],item['did_byte'],item['did_bit'],item['max_val'],item['min_val'])
            try:
                cursor.execute(into, values)
                db.commit()
            except Exception as e:
                printt('PEPS err ecuId: '+ecuId+' i: '+str(i))
                dealException(e)

            number=number+1

        break